﻿//------------------------------
// Author: Yang Shulin
// Version: V1.0
// DateTime: 2011-2-28
//------------------------------

using System.Text;

namespace Ysl.Data.SqlBuilder
{
    /// <summary>
    /// 组织Sql语句实现
    /// </summary>
    public class MsSqlBuilder : ISqlBuilder
    {
        #region 组织任意多条件的Select查询语句
        /// <summary>
        /// 组织任意多条件的Select查询语句
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="top">返回前top条数据记录（-1 表示符合条件的全部记录）</param>
        /// <param name="fields">需要得到的字段名</param>
        /// <param name="ascField">排序字段名称（默认按主键排序）</param>
        /// <param name="orderType">排序的类型，0 为升序，1 为降序（默认）</param>
        /// <param name="strWhere">查询条件，不用加where关键字，如："[Field1] = @Field1 and [Field2] = @Field2 and [Field3] > @Field3"</param>
        /// <returns>返回string类型</returns>
        public string Select(string tableName, int top, string fields, string ascField, int orderType, string strWhere)
        {
            StringBuilder strRes = new StringBuilder();

            if (top > 0)
            {
                strRes.Append("SELECT TOP ").Append(top.ToString()).Append(" ").Append(fields).Append(" FROM [").Append(tableName).Append("] ");
            }
            else
            {
                strRes.Append("SELECT ").Append(fields).Append(" FROM [").Append(tableName).Append("]");
            }

            if (!string.IsNullOrEmpty(strWhere))
            {
                strRes.Append(" WHERE ").Append(strWhere);
            }

            if (orderType > 0)
            {
                strRes.Append(" ORDER BY ").Append(ascField).Append(" DESC");
            }
            else
            {
                strRes.Append(" ORDER BY ").Append(ascField);
            }

            return strRes.ToString();
        }
        #endregion

        #region 组织任意多条件的分页查询SelectPager语句
        /// <summary>
        /// 组织任意多条件的分页查询SelectPager语句
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">需要得到的字段名</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="currentPage">当前第几页</param>
        /// <param name="ascField">排序的字段名，默认为按主键排序</param>
        /// <param name="orderType">排序的类型 (0为升序,1为降序)</param>
        /// <param name="strWhere">查询条件，不用加where关键字，如："[Field1] = @Field1 and [Field2] = @Field2 and [Field3] > @Field3"</param>
        /// <returns>返回string类型</returns>
        public string SelectPage(string tableName, string fields, int pageSize, int currentPage, string ascField, int orderType, string strWhere)
        {
            StringBuilder strRes = new StringBuilder();

            //声明变量
            strRes.Append("DECLARE @counts int;");                                      //保存查询到的总记录数
            strRes.Append("DECLARE @pageCounts int;");                              //保存查询到的总页数
            strRes.Append("DECLARE @pageSize int;");                                    //分页大小
            strRes.Append("DECLARE @currentPage int;");                                 //当前第几页

            //为变量赋值
            strRes.Append("SET @counts = 0;");                                          //保存查询到的总记录数
            strRes.Append("SET @pageCounts = 0;");                                      //保存查询到的总页数
            strRes.Append("SET @pageSize = ").Append(pageSize.ToString()).Append(";");              //分页大小
            strRes.Append("SET @currentPage = ").Append(currentPage.ToString()).Append(";");        //当前第几页
 

            //查询记录总数的查询语句
            if (!string.IsNullOrEmpty(strWhere))
            {
                strRes.Append("SELECT @counts = COUNT(*) FROM [").Append(tableName).Append("] WHERE ").Append(strWhere).Append(";");
            }
            else
            {
                strRes.Append("SELECT @counts = COUNT(*) FROM [").Append(tableName).Append("];");
            }

            //计算分页总数
            strRes.Append("SET @pageCounts = (@counts + @pageSize - 1) / @pageSize;");

            //调整当前页为有效值，当前页小于0时，取第一页，当前页大于总页数时，取最后一页
            strRes.Append("IF @currentPage < 1 SET @currentPage = 1;");
            strRes.Append("IF @currentPage > @pageCounts SET @currentPage = @pageCounts;");

            //处理开始点和结束点
            strRes.Append("DECLARE @startRecord int;");
            strRes.Append("DECLARE @endRecord int;");
            strRes.Append("SET @startRecord = (@currentPage - 1) * @pageSize + 1;");
            strRes.Append("SET @endRecord = @currentPage * @pageSize;");

            //查询分页数据
            strRes.Append("SELECT ").Append(fields).Append(" FROM (SELECT ROW_NUMBER() OVER(");
            if (orderType > 0)
            {
                strRes.Append(" ORDER BY ").Append(ascField).Append(" DESC ");
            }
            else
            {
                strRes.Append(" ORDER BY ").Append(ascField).Append(" ASC ");
            }
            if (!string.IsNullOrEmpty(strWhere))
            {
                strRes.Append(") AS rowNumber, ").Append(fields).Append(" FROM ").Append(tableName).Append(" WHERE ").Append(strWhere).Append(") AS tmpTable WHERE rowNumber BETWEEN  @startRecord  AND @endRecord;");
            }
            else
            {
                strRes.Append(") AS rowNumber, ").Append(fields).Append(" FROM ").Append(tableName).Append(") AS tmpTable WHERE rowNumber BETWEEN @startRecord AND @endRecord;");
            }

            //查询总记录数
            strRes.Append("SELECT @counts AS counts;");

            //查询总页数
            strRes.Append("SELECT @pageCounts AS pageCounts;");

            return strRes.ToString();
        }
        #endregion
    }
}
